<?php

// Acceso restringido
defined('JL') or die('Acceso restringido');

class NewsletterModel extends JLModelBase {

    /**
     * Insert Newsletter
     * @Retorna bool
     */
    public function insertNewsletter($var_nom_newsletter, $int_id_state) {
        $_true = false;

        $sql = "INSERT INTO 
              `tbl_newsletter`
            (
              `var_nom_newsletter`,
              `int_id_state`
            ) 
            VALUE(
              " . $this->DB->Quote($var_nom_newsletter) . ",
              " . $this->DB->Quote($int_id_state) . "
            )";

        $this->DB->executeQuery($sql);

        if ($this->DB->getRowsAffected() > 0) {
            $_true = $this->DB->getInsertedId();
        }

        return $_true;
    }

    /**
     * Get Newsletter
     * @Retorna bool
     */
    public function getNewsletter($id) {
        $_true = null;

        $sql = "SELECT 
              `int_id_newsletter`,
              `var_nom_newsletter`,
              `int_id_state`
            FROM 
              `tbl_newsletter`
            WHERE `int_id_newsletter` = " . $this->DB->Quote($id) . "
            LIMIT 1";

        $this->DB->executeQuery($sql);

        if ($this->DB->getRowsAffected() > 0) {
            $_true = $this->DB->getDataArray();
            $_true = $_true[0];
        }

        return $_true;
    }

    /**
     * Get Newsletters
     * @Retorna bool
     */
    public function getNewsletters() {
        $_true = null;

        $sql = "SELECT 
              `int_id_newsletter`,
              `var_nom_newsletter`,
              `int_id_state`
            FROM 
              `tbl_newsletter`
            WHERE `int_id_state` <> 3
            ORDER BY int_id_newsletter DESC";

        $this->DB->executeQuery($sql);

        if ($this->DB->getRowsAffected() > 0) {
            $_true = $this->DB->getDataArray();
        }

        return $_true;
    }

    /**
     * update Newsletter
     * @Retorna bool
     */
    public function updateNewsletter($int_id_newsletter, $var_nom_newsletter, $int_id_state) {
        $_true = false;

        $sql = "UPDATE 
              `tbl_newsletter`  
            SET
              `var_nom_newsletter` = " . $this->DB->Quote($var_nom_newsletter) . ",
              `int_id_state` = " . $this->DB->Quote($int_id_state) . "
            WHERE 
              `int_id_newsletter` = " . $this->DB->Quote($int_id_newsletter) . "";

        $this->DB->executeQuery($sql);

        if ($this->DB->getRowsAffected() > 0) {
            $_true = true;
        }

        return $_true;
    }

    /**
     * update Newsletter
     * @Retorna bool
     */
    public function updateNewsletterTxtNewsletter($int_id_newsletter, $txt_newsletter) {
        $_true = false;

        $sql = "UPDATE 
              `tbl_newsletter`  
            SET
              `txt_newsletter` = " . $this->DB->Quote($txt_newsletter) . "
            WHERE 
              `int_id_newsletter` = " . $this->DB->Quote($int_id_newsletter) . "";

        $this->DB->executeQuery($sql);

        if ($this->DB->getRowsAffected() > 0) {
            $_true = true;
        }

        return $_true;
    }

    /**
     * Elimina Newsletter
     * @Retorna bool
     */
    public function deleteNewsletter($id) {
        $_true = false;

        $sql = "DELETE FROM 
              `tbl_newsletter` 
            WHERE 
              `int_id_newsletter` = " . $this->DB->Quote($id) . " AND `int_id_state` <> 1";

        $this->DB->executeQuery($sql);

        if ($this->DB->getRowsAffected() > 0) {
            $_true = true;
        }

        return $_true;
    }

    /**
     * retorna Actualites Newsletter
     * @Retorna bool
     */
    public function getActualitesBoxByIdNewsLetter($id) {
        $_true = false;

        $sql = "SELECT 
            tbl_actualite_box.var_column,
            tbl_actualite_box.int_id_actualite,
            tbl_actualite_box.int_no_csme_actualite,
            tbl_actualite_box.int_order,
            tbl_actualite_box.int_id_newsletter,
            tbl_actualites.var_ac_title,
            tbl_actualites.var_monthyear,
            tbl_actualites.txt_content_actualite,
            tbl_sections.var_desc_section,
            tbl_actualites.int_id_section,
            tbl_actualites.txt_image_galleries
        FROM tbl_sections 
        INNER JOIN tbl_actualites ON (tbl_sections.int_id_section = tbl_actualites.int_id_section)
        INNER JOIN tbl_actualite_box ON (tbl_actualites.int_actualite = tbl_actualite_box.int_id_actualite)
        WHERE tbl_actualite_box.int_id_newsletter = $id
        ORDER BY tbl_actualite_box.var_column, tbl_actualite_box.int_order
        LIMIT 200";

        $this->DB->executeQuery($sql);

        if ($this->DB->getRowsAffected() > 0) {
            $_true = $this->DB->getDataArray();
        }

        return $_true;
    }

    public function getNewsletterBySent($state) {
        $sql = "SELECT 
              `int_id_newsletter`,
              `var_nom_newsletter`,
              `int_id_state`
            FROM 
              `tbl_newsletter`
            WHERE `int_id_state` <> 3 AND int_sent = $state
            ORDER BY int_id_newsletter DESC
            LIMIT 1";

        $this->DB->executeQuery($sql);

        $_true = NULL;

        if ($this->DB->getRowsAffected() > 0) {
            $_true = $this->DB->getDataArray();
        }

        return $_true;
    }

    public function updateNewsletterSent($int_id_newsletter, $sent) {
        $_true = FALSE;

        $sql = "UPDATE 
              `tbl_newsletter`  
            SET
              `int_sent` = $sent
            WHERE 
              `int_id_newsletter` = $int_id_newsletter";

        $this->DB->executeQuery($sql);
        
        return $this->DB->getRowsAffected();
    }
    public function updateNewslettersSent($sent) {
        $_true = FALSE;

        $sql = "UPDATE 
              `tbl_newsletter`  
            SET
              `int_sent` = $sent
            WHERE 
              `int_sent` <> $sent";

        $this->DB->executeQuery($sql);

        if ($this->DB->getRowsAffected() > 0) {
            $_true = TRUE;
        }

        return $_true;
    }
    

}